﻿#-*- coding: utf-8 -*-
u"""
Modulo utilizado no primeiro projecto da Disciplina de Linguagem de Programação
que consiste em elaboração de uma aplicação capaz de fazer leituras dos dados
num ficheiro excel(.xls) e a sua respectiva escrita dnum ficheiro de base de
dados no formato sqlite.
Utilizará os dados da base de daos para gerar quer lista deresultados
quer gráficos estatísticos representativos
de:
- Total de Colocados por Escola
- Total de Colocados por Distrito
- Total de Vagas Sobrantes por Escola
- Total de Vagas Sobrantes por Distrito
- Percentagem de Colocados por Escola
- Permilagem de Colocados por Distrito
"""
# modulo de operações de leitura e escrita sobre um ficheiro
# de base de dados sqlite3
# 
# IPBeja - Escola Superior de Tecnologia e Getão
# Engenharia Informática
# Elísio Gonçalves Ribeiro nº4708


##
###############################################################################

import sys, time, os
import string
import xlrd, csv
import opo_db as DB                                                #retirado de http://pypi.python.org/pypi/Unidecode
import g_graphics as bg


#variables
g_info = {}
##
###############################################################################
# metodo que verifica o nm_ficheiro fornecido tem a extenção correcta
# tenta abrir o nm_ficheiro e guarda uma ligacao do ficheiro aberto
#
def openRead_book( file_name, db_name):
    """
    verifies the extension of a given 'file_name' 
    if it's a excel file, try to open and read it's content
    in to a sqlite database file.
    file_name   - excel file name to be open and read (relative/absolute path)
    db_name     - sqlite file name(inc. path) where to save excel file content
    """
    r_book = None                                                              #uma referencia para guardar o documento fornecido
    try:                                                                       #em caso positivo
        r_book = xlrd.open_workbook(file_name, formatting_info=True)           #tenta abrir o documento para leitura
        res = readBk_SaveDb(r_book, db_name)                                   #ler os dados do livro e guradar na db
    except:
        raise
    else:
        return res
    pass
pass

# metodo que recebe uma ligação a um ficheiro excel a ler e o nome/endereço
# do ficheiro base de dados sqlite onde  escrever o conteúdo.
# cria as tabelas apartir dos dados das colunas
# para poupar nos cilcos 'for' faz neste mesmo metodo a leitura do livro e
# preenchimento das tabelas criadas
#
def readBk_SaveDb(xls_book, db_path):
    """
    connects to database; reads information on the opened book to create the 
    database tables based on columns info;
    loads the sheets data into db tables and commits the changes to the disk
    xls_book    - opened excel book connection
    db_path     - path/name to the sqlite dabase file
    """    
    try:
        conn = DB.connectingTo_db(db_path)                                         #ligacao a base de dados
        if (conn != None):
            cursor = conn.cursor()                                                 #criacao do cursor
            scl_data = []                                                          #lista para os dados das escolas
            course_data = []                                                       #lista para os dados dos cursos
            course_info = []                                                       #lista para os dados das vagas
            time1 = time.time()
            for p_nr in range(xls_book.nsheets):
                excel_leaf = xls_book.sheet_by_index(p_nr)                         #leitura de cada folha pelo indice
                __getting_tInfo(cursor, excel_leaf)                                #criacao das tabelas
                conn.commit()
                for ln in range(3,excel_leaf.nrows-2):
                    cell_val = excel_leaf.row_values(ln)
                    city = __getting_city(cell_val[2])                             #obtencao da cidade de cada escola
                    scl_data.append((cell_val[0], cell_val[2], city))              #dados da escola
                    course_data.append((cell_val[1], cell_val[3], cell_val[4]))    #dados dos cursos
                    course_info.append((ln-3, cell_val[5], cell_val[6],            #restantes dados
                                        cell_val[8], cell_val[7], cell_val[0],     #sobre cada cursos por escola
                                        cell_val[1]))  
                pass   
                scl_data = set(scl_data)
                course_data = set(course_data)
                table_data = {
                            "escolas":list(scl_data), 
                            "cursos":list(course_data), 
                            "concurso":course_info
                            }
                for t_name, t_values in table_data.items():
                    DB.filling_dbTable(cursor, t_values, t_name)
                conn.commit()
            pass
        pass
    except:
        raise
    else:
        DB.turnOff_conn(conn, cursor)                                          #tudo lido e gurdado entao desligar
        time2 = time.time()
        return (db_path, time2 - time1)
    pass
pass

# metodo para guardar os dados num ficheiro csv
#
def writeTo_csvFile(csv_file, res_lst):
    '''
    uses the func in f_query_db to query the db and returns a
    list of clumns names and data from those columns, then adds the prefix to 
    the csv filemane where to save db data and finally writes to csv file and 
    returns the lists
    res_lst      - data list to be saved to file
    csv_file    - CSV file name where to save the data list
    '''
    try:
        c_names = res_lst[0]
        res_lst = res_lst[1]
        csv_lst = __utf_8_encoder(res_lst)
        writer = csv.writer(open(csv_file, "wb"))
        writer.writerow(c_names)
        writer.writerows(csv_lst)
    except:
        raise
pass


###############################################################################
#codificando os dados fornecidos com unicode
#
def __unidecoding(list):
    """recebe uma lista de tuplos e converte os campos strings 
        retirando as acentuações das palavras
        lista   - conjunto de dados a converter
    """
    tmp_lst = []
    for cmp in list:
        cmp = cmp.split()
        if len(cmp) > 1: 
            cmp = cmp[0]+'_'+cmp[-1]
        else: cmp = cmp[0]        
        if (isinstance(cmp, unicode)):
            tmp_lst.append(cmp.encode('utf-8'))
        else:
            tmp_lst.append(cmp)
        pass
    pass
    return tmp_lst
pass

# metodo que faz a leitura dos dados de cada folha excel necessarios
# para a criacao das tabelas na base de dados
#
def __getting_tInfo(cursor, excel_leaf):
    """
    reads the first leaf line to retrieve columns info into
    col_data;normalizes it to be used as db tables columns names
    then creates the db tables
    """
    col_data = excel_leaf.row_values(1)
    col_data = __unidecoding(col_data)
    db_tables = {"escolas":[], "cursos":[], "concurso":[]}
    
    db_tables["escolas"].append((col_data[0],'TEXT'))
    db_tables["escolas"].append((col_data[2],'TEXT'))
    db_tables["escolas"].append(('Cidade','TEXT'))
    db_tables["cursos"].append((col_data[1],'TEXT'))
    db_tables["cursos"].append((col_data[3],'TEXT'))
    db_tables["cursos"].append((col_data[4],'TEXT'))
    db_tables["concurso"].append(('ID','INTEGER'))
    db_tables["concurso"].append((col_data[5],'INTEGER'))
    db_tables["concurso"].append((col_data[6],'INTEGER'))
    db_tables["concurso"].append((col_data[8],'INTEGER'))
    db_tables["concurso"].append((col_data[7],'REAL'))
    
    tname = db_tables.keys()[0]
    tcol = db_tables[db_tables.keys()[0]]
    f_keys = [(db_tables.keys()[1], db_tables[db_tables.keys()[1]][0])]        #nomes das tabelas e 
    f_keys.append((db_tables.keys()[2], db_tables[db_tables.keys()[2]][0]))    #as chaves estrangeiras
    DB.creating_tables(cursor, tname, tcol, f_keys)                            #criacao da tabela
    
    tname = db_tables.keys()[1]
    tcol = db_tables[db_tables.keys()[1]]
    DB.creating_tables(cursor,  tname, tcol)                                   #criacao da tabela
    
    tname = db_tables.keys()[2]
    tcol = db_tables[db_tables.keys()[2]]
    DB.creating_tables(cursor,  tname, tcol)   #criacao da tabela
pass

#utiliza operações de strings para subtrais a cada nome de escola a 
#sua localização/nome da cidade onde está situado
#
def __getting_city(scl_name):
    """
    gets the complete school name then computes the 
    school location derived from its name and return it
    """
    city = scl_name.split(' - ')[0]
    city = city.split()[-1]
    if city == 'Branco': city = 'Castelo Branco'
    elif city == 'Castelo': city = 'Viana do Castelo'
    elif city == 'Interior': city = 'Beira Interior'
    elif city == 'Henrique': city = 'Lisboa'
    elif city == 'Douro': city = 'Tras-os-Montes e Alto Douro'
    elif city == 'ISCTE': city = 'Lisboa'
    
    return city
pass

#translate ascii characters
def __utf_8_encoder(list):
#adapted from http://docs.python.org/release/2.5.2/lib/csv-examples.html
    '''codifica a informação em utf8 e retorna-os numa lista'''
    r_lst = []
    for tp in list:
        n_lst = []
        n_lst.append(tp[0])
        n_lst.append(tp[1].encode("utf8"))
        [n_lst.append(vlw) for vlw in tp[2:]]
        r_lst.append(tuple(n_lst))
    pass
    return r_lst
pass

#formatar o nome de cada ficheiro para resultado
def __format_name(file, prefix):
    """
    adds a prefix to a filename
    """
    dir = os.path.dirname(file)
    fname = os.path.basename(file)
    fname = prefix + fname
    file = os.path.join(dir, fname)
    return file
pass



##
###############################################################################
#obter lista de vagas sobrantes por escolas
def getFPlaces_PerSchool(dbname):
    """
    queries the database and returns a list of places that 
    have not been ocupied by any student. 
    the list contains free places for each school
    dbname  - dbase name where to query/retrieve the list from
    """
    return DB.getFPlaces_PerSchool(dbname)
pass

#obter lista de vagas sobrantes por distrito
def getFPlaces_PerState(dbname):
    """
    queries the database and returns a list of places that 
    have not been ocupied by any student. 
    the list contains the sums of free places for each state
    dbname  - dbase name where to query/retrieve the list from
    """
    return DB.getFPlaces_PerState(dbname)
pass

#obter lista de colocados por distrito
def getPlaced_PerState(dbname):
    """
    queries the database and returns a list of 
    total placed students for each state
    dbname  - dbase name where to query/retrieve the list from
    """
    return DB.getPlaced_PerState(dbname)
pass

#obter lista de colocados por escolas
def getPlaced_PerSchool(dbname):
    """
    queries the database and returns a list of 
    total placed students for each school
    dbname  - dbase name where to query/retrieve the list from
    """
    return DB.getPlaced_PerSchool(dbname)
pass

#calcular a percentagem de colocados por escolas
#em relacao ao total de colocados
def getPerc_Placed(dbname):
    '''
    queries the database and returns a list of 
    percentual value of placed students per school
    dbname  - dbase name where to query/retrieve the list from
    '''
    
    return DB.getPerc_Placed(dbname)
pass

#calcular o valor permilagem dos colocados por distrito
def getPermil_Placed(dbname):
    '''
    queries the database and returns a list of 
    percentual value of placed students per state
    dbname  - dbase name where to query/retrieve the list from
    '''
    
    return DB.getPermil_Placed(dbname)
pass

#retornar a lista de mais listas de dadps por cursos
def get_all_places(dbname):
    '''
    queries the database and returns a list of 
    value of total initial available places, filled places, and remainder
    dbname  - dbase name where to query/retrieve the list from
    '''
    return DB.get_all_places(dbname)
pass

#por cada opcao passada collecta os dados necessarios e 
#gera o respectivo grafico de barras
#option - opcao do menu do grafico escolhido
#q_result - dicionario com os dados sobre o grafico pedido
#svfname - nome a atribuir ao grafico gerado
def gen_graphics(option, q_result, total_school, total_state):
    """
    for each menu option collect data preparing list of labels
    list of xx values, yy values, title for the graphic, color,
    list of values for default graphic and filename to 
    generate the requested graphic
    option      - option selected from the menu,
    q_result    - a dict object where to extract xx and yy list of values,
    svfname     - filename to save the generated graphic
    """
    if option == '01':
        labels = q_result[0]
        labels = labels[0::2]                                                  #obter os labels
        q_data = q_result[1]
        q_data = filter(lambda x: x[2] > 0, q_data)                            #descartar os resultados a zero
        lst_xx = map(lambda v1: v1[0], q_data)                                 #obter lista de valores xx
        lst_yy = map(lambda v1: v1[2], q_data)                                 #obter lista de valores yy

        params = ((labels, lst_xx, lst_yy), 
        'Vagas Sobrantes por Escolas', 'm', total_school, option)    #preparar os paramentros
        svfname = bg.draw_graphic(*params)                                     #chamada de geracao de graficos
    elif option == '02':
        labels = q_result[0]
        labels = labels[0::2]
        q_data = q_result[1]
        q_data = filter(lambda x: x[2] > 0, q_data)
        lst_xx = map(lambda v1: v1[1], q_data)
        lst_yy = map(lambda v1: v1[2], q_data)
        
        params = ((labels, lst_xx, lst_yy), 
        'Vagas Sobrantes por Distritos', 'b', total_state,option)
        svfname = bg.draw_graphic(*params)
    elif option == '03':
        labels = q_result[0]
        labels = labels[0::2]
        q_data = q_result[1]
        q_data = filter(lambda x: x[2] > 0, q_data)
        lst_xx = map(lambda v1: v1[0], q_data)
        lst_yy = map(lambda v1: v1[2], q_data)
        
        params = ((labels, lst_xx, lst_yy), 
        'Total de Colcados por Escolas', 'y', total_school,option)
        svfname = bg.draw_graphic(*params)
    elif option == '04':
        labels = q_result[0]
        labels = labels[0::2]
        q_data = q_result[1]
        q_data = filter(lambda x: x[2] > 0, q_data)
        lst_xx = map(lambda v1: v1[1], q_data)
        lst_yy = map(lambda v1: v1[2], q_data)
        
        params = ((labels, lst_xx, lst_yy), 
        'Total de Colcados por Distritos', 'g', total_state,option)
        svfname = bg.draw_graphic(*params)
    elif option == '05':
        labels = q_result[0]
        labels = labels[0::3]
        q_data = q_result[1]
        lst_xx = map(lambda v1: v1[0], q_data)
        lst_yy = map(lambda v1: v1[3], q_data)

        tmp = list(total_school)                                 #ou uma pesquisa das vagas iniciais por escolas
        tmp[1] = zip(lst_xx, lst_yy)
        params = ((labels, lst_xx, lst_yy), 
        'Total de Colcados por Escolas(%)', 'c', tmp, option)
        svfname = bg.draw_graphic(*params)
    elif option == '06':
        labels = q_result[0]
        labels = labels[0::3]
        q_data = q_result[1]
        q_data = filter(lambda x: x[3] > 0, q_data)
        lst_xx = map(lambda v1: v1[1], q_data)
        lst_yy = map(lambda v1: v1[3], q_data)

        tmp = list(total_state)                                            #ou entao usar uma
        tmp[1] = zip(lst_xx, lst_yy)                                       #pesquisa de total de vagas iniciais por distrito
        params = ((labels, lst_xx, lst_yy), 
        'Total de Colcados por Distritos(%o)', 'r', tmp, option)
        svfname = bg.draw_graphic(*params)
    return svfname
pass
